﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.Security;

namespace GrayParrot.Web.Security
{
    [Flags]
    public enum ControlActions
    {
        Hidden = 0x1,
        Readonly = 0x2
    }

    [Serializable]
    public static class Controls
    {
        public static string ConnectionString { get; set; }
        public static string ApplicationName { get; set; }
        public static Exception LastException { get; set; }

        /// <summary>
        /// Creates the control in the page...
        /// </summary>
        /// <param name="pageName">Name of the control.</param> 
        /// <param name="pageName">Name of the page.</param>
        public static void CreateControl(string controlName, string pageName)
        {
            try
            {
                SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
                p1.Value = ApplicationName;
                SqlParameter p2 = new SqlParameter("@ControlName ", SqlDbType.NVarChar, 256);
                p2.Value = controlName;
                SqlParameter p3 = new SqlParameter("@PageName ", SqlDbType.NVarChar, 256);
                p3.Value = pageName;
                SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
                pRT.Direction = ParameterDirection.ReturnValue;

                SqlParameter[] m_params = new SqlParameter[4] { p1, p2, p3, pRT };

                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_Controls_CreateControl]", conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandTimeout = 600; // seconds
                        cmd.Parameters.AddRange(m_params);

                        conn.Open();

                        cmd.ExecuteNonQuery();

                        int returnValue = GetReturnValue(cmd);

                        switch (returnValue)
                        {
                            case 0:
                                return;
                            case 1:
                                throw new Exception(string.Format("Control {0} already exists in page: {1}", controlName, pageName));
                            case 2:
                                throw new Exception(string.Format("Page {0} not exists", pageName));
                            default:
                                throw new Exception("Unknown failure");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LastException = ex;
                throw;
            }
        }

        /// <summary>
        /// Deletes the control from the page...
        /// </summary>
        /// <param name="pageName">Name of the control.</param>
        /// <param name="pageName">Name of the page.</param>
        /// <returns></returns>
        public static bool DeleteControl(string controlName, string pageName)
        {
            bool retValue = false;

            try
            {
                SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
                p1.Value = ApplicationName;
                SqlParameter p2 = new SqlParameter("@ControlName ", SqlDbType.NVarChar, 256);
                p2.Value = pageName;
                SqlParameter p3 = new SqlParameter("@PageName ", SqlDbType.NVarChar, 256);
                p3.Value = pageName;
                SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
                pRT.Direction = ParameterDirection.ReturnValue;

                SqlParameter[] m_params = new SqlParameter[4] { p1, p2, p3, pRT };

                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_Controls_DeleteControl]", conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandTimeout = 600; // seconds
                        cmd.Parameters.AddRange(m_params);

                        conn.Open();

                        cmd.ExecuteNonQuery();

                        int returnValue = GetReturnValue(cmd);

                        if (returnValue == 1)
                        {
                            throw new Exception("Control not exists");
                        }

                        if (returnValue == 2)
                        {
                            throw new Exception("Page not exists");
                        }

                        retValue = (returnValue == 0);
                    }
                }
            }
            catch (Exception ex)
            {
                LastException = ex;
                throw;
            }

            return retValue;
        }

        /// <summary>
        /// Determines whether [is control of a page in role] [the specified controlname].
        /// </summary>
        /// <param name="pagename">The control name.</param>
        /// <param name="pagename">The page name.</param>
        /// <param name="roleName">Name of the role.</param>
        /// <returns>
        ///   <c>true</c> if [is control in role] [the specified controlname]; otherwise, <c>false</c>.
        /// </returns>
        /// <exception cref="System.Exception">Application Id wrong</exception>
        /// <exception cref="System.Exception">Page not exists</exception>
        /// <exception cref="System.Exception">Control not exists</exception>
        /// <exception cref="System.Exception">Role not exists</exception>
        /// <exception cref="System.Exception">Unknown failure</exception>
        public static bool IsControlInRole(string controlname, string pagename, string roleName)
        {
            bool retValue = false;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter p2 = new SqlParameter("@ControlName", SqlDbType.NVarChar, 256);
            p2.Value = controlname;
            SqlParameter p3 = new SqlParameter("@PageName", SqlDbType.NVarChar, 256);
            p3.Value = pagename;
            SqlParameter p4 = new SqlParameter("@RoleName", SqlDbType.NVarChar, 256);
            p4.Value = roleName;

            SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
            pRT.Direction = ParameterDirection.ReturnValue;

            SqlParameter[] m_params = new SqlParameter[5] { p1, p2, p3, p4, pRT };

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_ControlsInRoles_IsControlInRole]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    SqlParameter p = new SqlParameter("@ReturnValue", SqlDbType.Int);
                    p.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    int iStatus = GetReturnValue(cmd);

                    switch (iStatus)
                    {
                        case 0:
                            retValue = false;
                            break;
                        case 1:
                            retValue = true;
                            break;
                        case 2:
                            throw new Exception("Application Id wrong");
                        case 3:
                            throw new Exception(string.Format("Page {0} not exists", pagename));
                        case 4:
                            throw new Exception(string.Format("Role {0} not exists", roleName));
                        case 5:
                            throw new Exception(string.Format("Control {0} not exists", controlname));
                        default:
                            throw new Exception("Unknown failure");
                    }

                }
            }

            return retValue;
        }

        /// <summary>
        /// Determines whether [is control of a page in role] [the specified controlname] and return the associated action...
        /// </summary>
        /// <param name="pagename">The control name.</param>
        /// <param name="pagename">The page name.</param>
        /// <param name="roleName">Name of the role.</param>
        /// <returns>
        ///   <c>true</c> if [is control in role] [the specified controlname]; otherwise, <c>false</c>.
        /// </returns>
        /// <exception cref="System.Exception">Application Id wrong</exception>
        /// <exception cref="System.Exception">Page not exists</exception>
        /// <exception cref="System.Exception">Control not exists</exception>
        /// <exception cref="System.Exception">Role not exists</exception>
        /// <exception cref="System.Exception">Unknown failure</exception>
        public static int IsControlInRoleEx(string controlname, string pagename, string roleName)
        {
            int retValue = 0;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter p2 = new SqlParameter("@ControlName", SqlDbType.NVarChar, 256);
            p2.Value = controlname;
            SqlParameter p3 = new SqlParameter("@PageName", SqlDbType.NVarChar, 256);
            p3.Value = pagename;
            SqlParameter p4 = new SqlParameter("@RoleName", SqlDbType.NVarChar, 256);
            p4.Value = roleName;

            SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
            pRT.Direction = ParameterDirection.ReturnValue;

            SqlParameter[] m_params = new SqlParameter[5] { p1, p2, p3, p4, pRT };

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_ControlsInRoles_IsControlInRoleEx]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    SqlParameter p = new SqlParameter("@ReturnValue", SqlDbType.Int);
                    p.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    int iStatus = GetReturnValue(cmd);

                    if (iStatus > 0)
                    {
                        retValue = iStatus;
                    }
                    else
                    {
                        switch (iStatus)
                        {
                            case 0:
                                return 0;
                            case -2:
                                throw new Exception("Application Id wrong");
                            case -3:
                                throw new Exception(string.Format("Page {0} not exists", pagename));
                            case -4:
                                throw new Exception(string.Format("Role {0} not exists", roleName));
                            case -5:
                                throw new Exception(string.Format("Control {0} not exists", controlname));
                            default:
                                throw new Exception("Unknown failure");
                        }
                    }
                }
            }

            return retValue;
        }

        /// <summary>
        /// Adds the Controls to role.
        /// </summary>
        /// <param name="pagenames">The control name(s)...</param>
        /// <param name="pagenames">The page name...</param>
        /// <param name="roleName">Name of the role...</param>
        public static void AddControlsToRole(string[] controlnames, string pageName, string roleName)
        {
            AddControlsToRoles(controlnames, pageName, new string[] { roleName });
        }

        /// <summary>
        /// Adds the Controls to roles.
        /// </summary>
        /// <param name="pagenames">The control name(s)...</param>
        /// <param name="pagenames">The page name.</param>
        /// <param name="roleNames">The role name(s).</param>
        /// <exception cref="System.Exception">
        /// Control name not found.
        /// Role name not found.
        /// Page name not found.
        /// Control is already in role.
        /// </exception>
        /// <exception cref="System.ArgumentException">Control names cannot contain commas.</exception>
        /// <exception cref="System.ArgumentException">Role names cannot contain commas.</exception>
        public static void AddControlsToRoles(string[] controlnames, string pageName, string[] roleNames)
        {
            string s1 = String.Empty, s2 = String.Empty;

            foreach (string rolename in roleNames)
            {
                if (!Roles.RoleExists(rolename))
                {
                    throw new Exception("Role name not found.");
                }
            }

            foreach (string controlname in controlnames)
            {
                if (controlname.Contains(","))
                {
                    throw new ArgumentException("Control names cannot contain commas.");
                }

                foreach (string rolename in roleNames)
                {
                    if (IsControlInRole(controlname, pageName, rolename))
                    {
                        throw new Exception("Control is already in role.");
                    }
                }
            }

            int numPagesRemaing = controlnames.Length;
            while (numPagesRemaing > 0)
            {
                int iter;
                string allControls = controlnames[controlnames.Length - numPagesRemaing];
                numPagesRemaing--;
                for (iter = controlnames.Length - numPagesRemaing; iter < controlnames.Length; iter++)
                {
                    if (allControls.Length + controlnames[iter].Length + 1 >= 4000)
                        break;
                    allControls += "," + controlnames[iter];
                    numPagesRemaing--;
                }

                int numRolesRemaining = roleNames.Length;
                while (numRolesRemaining > 0)
                {
                    string allRoles = roleNames[roleNames.Length - numRolesRemaining];
                    numRolesRemaining--;
                    for (iter = roleNames.Length - numRolesRemaining; iter < roleNames.Length; iter++)
                    {
                        if (allRoles.Length + roleNames[iter].Length + 1 >= 4000)
                            break;
                        allRoles += "," + roleNames[iter];
                        numRolesRemaining--;
                    }

                    SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
                    p1.Value = ApplicationName;
                    SqlParameter p2 = new SqlParameter("@pageName", SqlDbType.NVarChar, 256);
                    p2.Value = pageName;
                    SqlParameter p3 = new SqlParameter("@controlNames", SqlDbType.NVarChar, 4000);
                    p3.Value = allControls;
                    SqlParameter p4 = new SqlParameter("@RoleNames", SqlDbType.NVarChar, 4000);
                    p4.Value = allRoles;
                    SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
                    pRT.Direction = ParameterDirection.ReturnValue;

                    SqlParameter[] m_params = new SqlParameter[5] { p1, p2, p3, p4, pRT };

                    using (SqlConnection conn = new SqlConnection(ConnectionString))
                    {
                        using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_ControlsInRoles_AddControlsToRoles]", conn))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandTimeout = 600; // seconds
                            cmd.Parameters.AddRange(m_params);

                            conn.Open();

                            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                            if (reader.Read())
                            {
                                if (reader.FieldCount > 0)
                                    s1 = reader.GetString(0);
                                if (reader.FieldCount > 1)
                                    s2 = reader.GetString(1);
                            }

                            switch (GetReturnValue(cmd))
                            {
                                case 0:
                                    return;
                                case 1:
                                    throw new Exception(string.Format("Control not found: {0} ", s1));
                                case 2:
                                    throw new Exception(string.Format("Role not found: {0}", s2));
                                case 3:
                                    throw new Exception(string.Format("This control already in role: {0} {1}", s1, s2));
                                case 10:
                                    throw new Exception(string.Format("Page {0} not found", pageName));
                                default:
                                    throw new Exception("Unknown failure");
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Adds the control to role.
        /// </summary>
        /// <param name="pagename">The control name.</param>
        /// <param name="pagename">The page name.</param>
        /// <param name="roleName">Name of the role.</param>
        public static void AddControlToRole(string controlname, string pagename, string roleName)
        {
            AddControlsToRoles(new string[] { controlname }, pagename, new string[] { roleName });
        }

        /// <summary>
        /// Adds the page to roles.
        /// </summary>
        /// <param name="pagename">The control name.</param> 
        /// <param name="pagename">The page name.</param>
        /// <param name="roleNames">The role names.</param>
        public static void AddControlToRoles(string controlname, string pagename, string[] roleNames)
        {
            AddControlsToRoles(new string[] { controlname }, pagename, roleNames);
        }

        /// <summary>
        /// Removes the control from role.
        /// </summary>
        /// <param name="controlname">The controlname.</param>
        /// <param name="pagename">The pagename.</param>
        /// <param name="roleName">Name of the role.</param>
        public static void RemoveControlFromRole(string controlname, string pagename, string roleName)
        {
            RemoveControlsFromRoles(new string[] { controlname }, pagename, new string[] { roleName });
        }

        /// <summary>
        /// Removes the control from roles.
        /// </summary>
        /// <param name="controlname">The controlname.</param>
        /// <param name="pagename">The pagename.</param>
        /// <param name="roleNames">The role names.</param>
        public static void RemoveControlFromRoles(string controlname, string pagename, string[] roleNames)
        {
            RemoveControlsFromRoles(new string[] { controlname }, pagename, roleNames);
        }

        /// <summary>
        /// Removes the controls from role.
        /// </summary>
        /// <param name="controlnames">The controlnames.</param>
        /// <param name="pagename">The pagename.</param>
        /// <param name="roleName">Name of the role.</param>
        public static void RemoveControlsFromRole(string[] controlnames, string pagename, string roleName)
        {
            RemoveControlsFromRoles(controlnames, pagename, new string[] { roleName });
        }

        /// <summary>
        /// Removes the controls from roles.
        /// </summary>
        /// <param name="controlnames">The controlnames.</param>
        /// <param name="pagename">The pagename.</param>
        /// <param name="roleNames">The role names.</param>
        /// <exception cref="System.Exception">
        /// Application id not found
        /// Page not found
        /// Role not found
        /// Control not found
        /// Control already exists in role
        /// Unknown failure
        /// </exception>
        public static void RemoveControlsFromRoles(string[] controlnames, string pagename, string[] roleNames)
        {
            string s1 = String.Empty, s2 = String.Empty;

            int numPagesRemaing = controlnames.Length;
            while (numPagesRemaing > 0)
            {
                int iter;
                string allControls = controlnames[controlnames.Length - numPagesRemaing];
                numPagesRemaing--;
                for (iter = controlnames.Length - numPagesRemaing; iter < controlnames.Length; iter++)
                {
                    if (allControls.Length + controlnames[iter].Length + 1 >= 4000)
                        break;
                    allControls += "," + controlnames[iter];
                    numPagesRemaing--;
                }

                int numRolesRemaining = roleNames.Length;
                while (numRolesRemaining > 0)
                {
                    string allRoles = roleNames[roleNames.Length - numRolesRemaining];
                    numRolesRemaining--;
                    for (iter = roleNames.Length - numRolesRemaining; iter < roleNames.Length; iter++)
                    {
                        if (allRoles.Length + roleNames[iter].Length + 1 >= 4000)
                            break;
                        allRoles += "," + roleNames[iter];
                        numRolesRemaining--;
                    }

                    /*  ccccc */
                    SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
                    p1.Value = ApplicationName;
                    SqlParameter p2 = new SqlParameter("@PageName", SqlDbType.NVarChar, 256);
                    p2.Value = pagename;
                    SqlParameter p3 = new SqlParameter("@ControlNames", SqlDbType.NVarChar, 4000);
                    p3.Value = allControls;
                    SqlParameter p4 = new SqlParameter("@RoleNames", SqlDbType.NVarChar, 4000);
                    p4.Value = allRoles;
                    SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
                    pRT.Direction = ParameterDirection.ReturnValue;

                    SqlParameter[] m_params = new SqlParameter[5] { p1, p2, p3, p4, pRT };

                    using (SqlConnection conn = new SqlConnection(ConnectionString))
                    {
                        using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_ControlsInRoles_RemoveControlsFromRoles]", conn))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandTimeout = 600; // seconds
                            cmd.Parameters.AddRange(m_params);

                            conn.Open();

                            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

                            if (reader.Read())
                            {
                                if (reader.FieldCount > 0)
                                    s1 = reader.GetString(0);
                                if (reader.FieldCount > 1)
                                    s2 = reader.GetString(1);
                            }

                            switch (GetReturnValue(cmd))
                            {
                                case 0:
                                    return;
                                case 1:
                                    throw new Exception("Application id not found");
                                case 2:
                                    throw new Exception(string.Format("Page {0} not found", pagename));
                                case 3:
                                    throw new Exception(string.Format("Role not found: {0}", s2));
                                case 4:
                                    throw new Exception(string.Format("Control {0} not found", s1));
                                case 5:
                                    throw new Exception(string.Format("Control already not in role: {0} {1}", s1, s2));
                                default:
                                    throw new Exception("Unknown failure");
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Gets all controls registered per application id...
        /// </summary>
        /// <returns></returns>
        public static string[] GetAllControls()
        {
            string[] retValue = null;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
            pRT.Direction = ParameterDirection.ReturnValue;

            SqlParameter[] m_params = new SqlParameter[2] { p1, pRT };

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_Controls_GetAllControls]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    conn.Open();

                    SqlDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        StringCollection sc = new StringCollection();
                        while (reader.Read())
                        {
                            sc.Add(reader.GetString(0));
                        }

                        retValue = new string[sc.Count];
                        sc.CopyTo(retValue, 0);
                    }
                }
            }

            return retValue;
        }

        /// <summary>
        /// Gets all controls in a page per application id...
        /// </summary>
        /// <returns></returns>
        public static string[] GetAllControlsInPage(string pageName)
        {
            string[] retValue = null;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter p2 = new SqlParameter("@PageName", SqlDbType.NVarChar, 256);
            p2.Value = pageName;
            SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
            pRT.Direction = ParameterDirection.ReturnValue;

            SqlParameter[] m_params = new SqlParameter[3] { p1, p2, pRT };

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_Controls_GetAllControlsInPage]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    conn.Open();

                    SqlDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        StringCollection sc = new StringCollection();
                        while (reader.Read())
                        {
                            sc.Add(reader.GetString(0));
                        }

                        retValue = new string[sc.Count];
                        sc.CopyTo(retValue, 0);
                    }
                }
            }

            return retValue;
        }

        /// <summary>
        /// Gets the roles for control.
        /// </summary>
        /// <param name="controlname">The control name.</param>
        /// <param name="pageName">Name of the page.</param>
        /// <returns></returns>
        /// <exception cref="System.Exception">
        /// Application Id not found
        /// Page not found
        /// Control not found
        /// Unknown failure
        /// </exception>
        public static string[] GetRolesForControl(string controlname, string pageName)
        {
            string[] retValue = null;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter p2 = new SqlParameter("@PageName", SqlDbType.NVarChar, 256);
            p2.Value = pageName;
            SqlParameter p3 = new SqlParameter("@ControlName", SqlDbType.NVarChar, 256);
            p3.Value = pageName;

            SqlParameter[] m_params = new SqlParameter[3] { p1, p2, p3};

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_ControlsInRoles_GetRolesForControl]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    SqlParameter p = new SqlParameter("@ReturnValue", SqlDbType.Int);
                    p.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(p);

                    conn.Open();

                    SqlDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        StringCollection sc = new StringCollection();
                        while (reader.Read())
                        {
                            sc.Add(reader.GetString(0));
                        }

                        retValue = new string[sc.Count];
                        sc.CopyTo(retValue, 0);

                        if (sc.Count < 1)
                        {
                            switch (GetReturnValue(cmd))
                            {
                                case 0:
                                    retValue = null;
                                    break;
                                case 1:
                                    throw new Exception("Application Id not found");
                                case 2:
                                    throw new Exception(string.Format("Page {0} not found", pageName));
                                case 3:
                                    throw new Exception(string.Format("Control {0} not found", controlname));
                                default:
                                    throw new Exception("Unknown failure");
                            }
                        }
                    }
                }
            }

            return retValue;
        }

        /// <summary>
        /// Gets the controls in role.
        /// </summary>
        /// <param name="pageName">Name of the page.</param>
        /// <param name="roleName">Name of the role.</param>
        /// <returns></returns>
        /// <exception cref="System.Exception">
        /// Application ID not found
        /// Page not found
        /// Role not found
        /// Unknown failure
        /// </exception>
        public static string[] GetControlsInRole(string pageName, string roleName)
        {
            string[] retValue = null;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter p2 = new SqlParameter("@PageName", SqlDbType.NVarChar, 256);
            p2.Value = pageName;
            SqlParameter p3 = new SqlParameter("@RoleName", SqlDbType.NVarChar, 256);
            p3.Value = roleName;
            SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
            pRT.Direction = ParameterDirection.ReturnValue;

            SqlParameter[] m_params = new SqlParameter[4] { p1, p2, p3, pRT };

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_ControlsInRoles_GetControlsInRole]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    conn.Open();

                    SqlDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        StringCollection sc = new StringCollection();
                        while (reader.Read())
                        {
                            sc.Add(reader.GetString(0));
                        }

                        retValue = new string[sc.Count];
                        sc.CopyTo(retValue, 0);

                        if (sc.Count < 1)
                        {
                            switch (GetReturnValue(cmd))
                            {
                                case 0:
                                    retValue = null;
                                    break;
                                case 1:
                                    throw new Exception("Application ID not found");
                                case 2:
                                    throw new Exception(string.Format("Page {0} not found", pageName));
                                case 3:
                                    throw new Exception(string.Format("Role {0} not found", roleName));
                                default:
                                    throw new Exception("Unknown failure");
                            }
                        }
                    }
                }
            }

            return retValue;
        }

        /// <summary>
        /// Controls the exists.
        /// </summary>
        /// <param name="controlName">Name of the control.</param>
        /// <param name="pageName">Name of the page.</param>
        /// <returns></returns>
        /// <exception cref="System.Exception">
        /// application id not found
        /// Page not found
        /// Unknown failure
        /// </exception>
        public static bool ControlExists(string controlName, string pageName)
        {
            bool retValue = false;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter p2 = new SqlParameter("@ControlName", SqlDbType.NVarChar, 256);
            p2.Value = controlName;
            SqlParameter p3 = new SqlParameter("@PageName", SqlDbType.NVarChar, 256);
            p3.Value = pageName;
            SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
            pRT.Direction = ParameterDirection.ReturnValue;

            SqlParameter[] m_params = new SqlParameter[4] { p1, p2, p3, pRT };

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_Controls_ControlExists]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    int returnValue = GetReturnValue(cmd);

                    switch (returnValue)
                    {
                        case 0:
                            retValue = false;
                            break;
                        case 1:
                            retValue = true;
                            break;
                        case 2:
                            throw new Exception("application id not found");
                        case 3:
                            throw new Exception(string.Format("Page {0} not found", pageName));
                        default:
                            throw new Exception("Unknown failure");
                    }
                }
            }

            return retValue;
        }

        /// <summary>
        /// Finds the controls in role.
        /// </summary>
        /// <param name="roleName">Name of the role.</param>
        /// <param name="pagename">The pagename.</param>
        /// <returns></returns>
        /// <exception cref="System.Exception">
        /// Application Id not found
        /// Page not found
        /// Role not found
        /// Unknown failure
        /// </exception>
        public static string[] FindControlsInRole(string roleName, string pagename)
        {
            string[] retValue = null;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter p2 = new SqlParameter("@PageName", SqlDbType.NVarChar, 256);
            p2.Value = pagename;
            SqlParameter p3 = new SqlParameter("@RoleName", SqlDbType.NVarChar, 256);
            p3.Value = roleName;
            SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
            pRT.Direction = ParameterDirection.ReturnValue;

            SqlParameter[] m_params = new SqlParameter[4] { p1, p2, p3, pRT };

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_ControlsInRoles_GetControlsInRole]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    conn.Open();

                    StringCollection sc = new StringCollection();
                    SqlDataReader reader = null;

                    try
                    {
                        reader = cmd.ExecuteReader();

                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                sc.Add(reader.GetString(0));
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        if (reader != null)
                            reader.Close();
                    }

                    retValue = new string[sc.Count];
                    sc.CopyTo(retValue, 0);

                    if (sc.Count < 1)
                    {
                        switch (GetReturnValue(cmd))
                        {
                            case 0:
                                retValue = null;
                                break;
                            case 1:
                                throw new Exception("Application Id not found");
                            case 2:
                                throw new Exception(string.Format("Page {0} not found", pagename));
                            case 3:
                                throw new Exception(string.Format("Role {0} not found", roleName));
                            default:
                                throw new Exception("Unknown failure");
                        }
                    }

                }
            }

            return retValue;
        }

        private static int GetReturnValue(SqlCommand cmd)
        {
            foreach (SqlParameter param in cmd.Parameters)
            {
                if (param.Direction == ParameterDirection.ReturnValue && param.Value != null && param.Value is int)
                    return (int)param.Value;
            }
            return -1;
        }

        /// <summary>
        /// Gets the controls in roles.
        /// </summary>
        /// <param name="pageName">Name of the page.</param>
        /// <returns></returns>
        /// <exception cref="System.Exception">
        /// Application Id not found
        /// Page not found
        /// Unknown failure
        /// </exception>
        public static DataTable GetControlsInRoles(string pageName)
        {
            DataTable retValue = null;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter p2 = new SqlParameter("@PageName", SqlDbType.NVarChar, 256);
            p2.Value = pageName;
            SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
            pRT.Direction = ParameterDirection.ReturnValue;

            SqlParameter[] m_params = new SqlParameter[3] { p1, p2, pRT };

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_ControlsInRoles_GetControlsInRoles]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    conn.Open();

                    SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    retValue = new DataTable();
                    adp.Fill(retValue);

                    switch (GetReturnValue(cmd))
                    {
                        case 0:
                            break;
                        case 1:
                            throw new Exception(string.Format("ApplicationName not found: {0}", ApplicationName));
                        case 2:
                            throw new Exception(string.Format("Page {0} not found", pageName));
                        default:
                            throw new Exception("Unknown failure");
                    }
                }
            }

            return retValue;
        }

        /// <summary>
        /// Gets the page identifier.
        /// </summary>
        /// <param name="pageName">Name of the page.</param>
        /// <returns></returns>
        /// <exception cref="System.Exception">
        /// Unknown failure
        /// </exception>
        public static Guid GetControlId(string controlName, string pageName)
        {
            Guid retValue = Guid.Empty;

            SqlParameter p1 = new SqlParameter("@ApplicationName", SqlDbType.NVarChar, 256);
            p1.Value = ApplicationName;
            SqlParameter p2 = new SqlParameter("@ControlName", SqlDbType.NVarChar, 256);
            p2.Value = controlName;
            SqlParameter p3= new SqlParameter("@pageName", SqlDbType.NVarChar, 256);
            p3.Value = pageName;
            SqlParameter pRT = new SqlParameter("@ReturnValue", SqlDbType.Int);
            pRT.Direction = ParameterDirection.ReturnValue;

            SqlParameter[] m_params = new SqlParameter[4] { p1, p2, p3, pRT };

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("[dbo].[aspnet_Controls_GetControlId]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 600; // seconds
                    cmd.Parameters.AddRange(m_params);

                    conn.Open();

                    SqlDataReader reader = null;
                    reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            retValue = Guid.Parse(reader.GetString(0));
                        }
                    }

                    switch (GetReturnValue(cmd))
                    {
                        case 0:
                            break;
                        case 1:
                            throw new Exception(string.Format("ApplicationName not found: {0}", ApplicationName));
                        case 2:
                            throw new Exception(string.Format("Page {0} not found", pageName));
                        default:
                            throw new Exception("Unknown failure");
                    }
                }
            }

            return retValue;
        }
    }
}
